﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;


namespace ClientApplication
{
    public partial class LoadMng : Form
    {
        //
        private DBServer dbServer = new DBServer();
        //保存打开的excel中的数据
        private DataSet data = new DataSet("EXCEL");
        //指标类型
        string tag = null;
        //年份
        string year = null;
        public LoadMng()
        {
            InitializeComponent();
        }
        
        public LoadMng(DBServer db) 
        {
            InitializeComponent();

            this.dbServer = db;
        }
        private void btn_open_Click(object sender, EventArgs e)
        {
            //打开excel文件的按钮点击事件处理函数
            //初始化一个OpenFileDialog对象
            this.openFileDialog = new OpenFileDialog();
            //判断用户是否正确的选择了文件
            if (this.openFileDialog.ShowDialog() == DialogResult.OK)
            {
                //选择框中过滤文件类型
                this.openFileDialog.Filter = "Excel Files(*.xls)|*.xls";
                string fileName = openFileDialog.FileName;
                this.textBox_filename.Text = fileName;
                //读取excel文件
                data = readExcelFile(fileName);
                //加载到DataGrid中显示
                DataTable table = data.Tables[0];
                this.dataGridView1.DataSource = table;

            }

        }

        private DataSet readExcelFile(string filePath)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();

                String sql = "SELECT * FROM  [Sheet1$]";//可是更改Sheet名称，比如sheet2，等等 

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "Sheet1");
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
        }

        private void LoadMng_Load(object sender, EventArgs e)
        {
            //对DataGrid的外观样式进行初始化
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
            //对指标combox外观样式进行初始化
            //this.comboBox1.DropDownWidth = 150;
            this.comboBox1.Items.AddRange(new object[] {"GDP",
                        "Population",
                        "PerPersonGDP",
                        "FirstIndustry",
                        "SecondIndustry",
            "ThirdIndustry"});
        }

        private void btn_ok_Click(object sender, EventArgs e)
        {
            year = this.textBox_year.Text;
            //MessageBox.Show(year);
            tag = this.comboBox1.SelectedItem.ToString();
            //MessageBox.Show(tag);
            //MessageBox.Show(data != null ? "not null" : "is null");
            //必须先判断输入的年份和指标不能为空
            if (!string.IsNullOrEmpty(tag.Trim()) && !string.IsNullOrEmpty(year.Trim()) && data != null)
            {
                if (tag.Equals("GDP"))
                {
                    //构造一个List用来存放所有的gdp对象
                    List<GISEntity.GDP> GDPList = new List<GISEntity.GDP>();
                    using (GISEntity.HnMapEntities database = new GISEntity.HnMapEntities(this.dbServer.GetConnectString()))
                    {
                        //遍历dataset进行数据写入                        
                        foreach (DataRow row in data.Tables[0].Rows)
                        {
                            ClientApplication.DTO.GDP gdp = new DTO.GDP();
                            gdp.CityName = row.ItemArray[0].ToString();
                            gdp.GDPValue = Convert.ToDecimal(row.ItemArray[1]);
                            gdp.Time = Convert.ToInt32(this.textBox_year.Text);
                            GDPList.Add(gdp.getDatabaseGDP());
                        }
                        //遍历GDPList存入entity framework上下文中
                        foreach (GISEntity.GDP item in GDPList)
                        {
                            //MessageBox.Show(item.OBJECTID.ToString());
                            database.GDPs.AddObject(item);
                        }
                        //保存更改
                        if (database.SaveChanges() > 0)
                        {
                            //
                            MessageBox.Show("数据添加成功", "数据添加成功", MessageBoxButtons.OK, MessageBoxIcon.Information);

                            this.Close();
                        }
                    }

                }
                else if (tag.Equals("Population"))
                {
                    //构造一个List用来存放所有的gdp对象
                    List<GISEntity.POPULATION> PopList = new List<GISEntity.POPULATION>();
                    using (GISEntity.HnMapEntities database = new GISEntity.HnMapEntities(this.dbServer.GetConnectString()))
                    {
                        //遍历dataset进行数据写入                        
                        foreach (DataRow row in data.Tables[0].Rows)
                        {
                            ClientApplication.DTO.Population pop = new DTO.Population();
                            pop.CityName = row.ItemArray[0].ToString();
                            pop.PopulationValue = Convert.ToDecimal(row.ItemArray[1]);
                            pop.Time = Convert.ToInt32(this.textBox_year.Text);
                            PopList.Add(pop.getDatabaseGDP());
                        }
                        //遍历GDPList存入entity framework上下文中
                        foreach (GISEntity.POPULATION item in PopList)
                        {
                            //MessageBox.Show(item.OBJECTID.ToString());
                            database.POPULATIONs.AddObject(item);
                        }
                        //保存更改
                        if (database.SaveChanges() > 0)
                        {
                            //
                            MessageBox.Show("数据添加成功", "数据添加成功", MessageBoxButtons.OK, MessageBoxIcon.Information);

                            this.Close();
                        }
                    }
                }
            }
            else
            {
                MessageBox.Show("请检查数据内容", "添加错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //if (this.comboBox1.SelectedItem != null)
            //{

            //    if (!this.comboBox1.SelectedItem.Equals("GDP") || !this.comboBox1.SelectedItem.Equals("Population"))
            //    {

            //        MessageBox.Show("请选择GDP或者POPULATION经济指标", "经济指标选择错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);

            //        this.comboBox1.SelectedItem = this.comboBox1.Items[this.comboBox1.Items.IndexOf("GDP")];
            //    }
            //}


        }
    }
}
